Many data storage applications use or interact with a database. The database may be, for example, a structured query language (SQL) database. SQL databases may operate in a single monolithic file mode where all of the information about the tables in the database is stored in a single monolithic file. For example, a standard database (db) file called ibdata may store the system tablespace for a database infrastructure. The ibdata file may store table data pages, table index pages, table metadata, multi-versioning concurrency control (MVCC) data, a data dictionary, an insert buffer, and other data about tables or data used to manage tables. The ibdata file may store this information for all tables in the db. Recognized problems associated with storing all the information for the tables in a db in a single file include wasted space, fragmentation, and the inability to distribute data.
FIG. 1 illustrates a master SQL database (db) 100. “Master” is used in its database terminology usage (e.g., master/slave). The master SQL db 100 is configured in a single monolithic file mode (SMFM) where data about all the tables in db 100 is stored in a single file 130. For example, all the information about table1 120, and table2 122 through tableN 128 is stored in the single file 130.
SQL databases may also operate in a mode different than the single monolithic file mode. For example, an SQL database may operate in a one table per file and one file per table mode. In this mode, there is a one to one correspondence between files and tables. Each file is associated with exactly one table and each table is associated with exactly one file. One table per file and one file per table mode facilitates performing compression, defragmentation, reclaiming wasted space, and other activities.
FIG. 2 illustrates a master SQL db 200 that is configured in one table per file mode (OTPFM). In this configuration, information about tables is stored in separate files. For example, information about table1 220 is stored in file1 230, information about table2 222 is stored in file2 232 and so on through tableN 228 and fileN 238.
While SQL databases may operate in the two different modes, conventionally it has been extremely time consuming to convert from one mode to another mode. In particular, converting from single file mode to one table per file and one file per table mode has required enduring the lengthy process of locking the database, dumping the database, stopping the database, deleting the database, and then waiting while data is imported back into a new database in the different format. After the dump, delete, and import is complete, the system can finally be brought back on-line for use. However, the dump, delete, and import process may take hours to complete, which is unacceptable in many environments requiring near continuous availability.